import sqlite3
from cp import CommandParse
import ast
from accessdatabase2 import download_file_from_server
import sys
import os
import subprocess
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QFont
from datetime import datetime,timedelta
from ds2 import get_sensor_data, calculate_timeout
from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QHBoxLayout, QLineEdit, QPushButton, QTableWidget, QTableWidgetItem, QLabel, QMessageBox, QDialog

class ProcessingWindow(QDialog):
    def __init__(self, data, parent=None):
        super().__init__(parent)
        self.setWindowTitle('Processed Data')
        self.setGeometry(150, 150, 600, 400)
        self.layout = QVBoxLayout()
        self.setLayout(self.layout)

        # Display each line of data
        for line in data:
            label = QLabel(line)
            self.layout.addWidget(label)

class DetailsWindow(QDialog):
    def __init__(self, row_data, headers, parent=None):
        super().__init__(parent)
        self.setWindowTitle('Row Details')
        self.setGeometry(150, 150, 400, 300)
        self.layout = QVBoxLayout()
        self.setLayout(self.layout)


        # Display each column data
        for header, data in zip(headers, row_data):
            label = QLabel(f"{header}: {data}")
            self.layout.addWidget(label)

class DatabaseViewer(QWidget):
    def __init__(self, db_path):
        super().__init__()
        self.db_path = db_path
        self.initUI()
        self.loadDatabase()

    def initUI(self):
        self.setWindowTitle('SQLite Database Viewer')
        self.setGeometry(100, 100, 2000, 600)

        self.layout = QVBoxLayout()
        self.setLayout(self.layout)

        # Search layout
        self.searchLayout = QHBoxLayout()
        self.layout.addLayout(self.searchLayout)

        self.searchLabel = QLabel('Search:')
        self.searchLayout.addWidget(self.searchLabel)

        self.searchInput = QLineEdit()
        self.searchLayout.addWidget(self.searchInput)

        self.searchButton = QPushButton('Search')
        self.searchButton.clicked.connect(self.search)
        self.searchLayout.addWidget(self.searchButton)

        # Table layout
        self.table = QTableWidget()
        self.table.setVerticalHeaderLabels(["传感器地址", '传感器类型', '代码类型','设备地址','信号强度','传感器值','电池电量','灯光状态','驱动版本','硬件版本','异常个数','异常值','上传时间'])
        self.table.cellDoubleClicked.connect(self.showDetails)  # Connect double click event to showDetails method
        self.table.verticalHeader().setVisible(False)
        self.layout.addWidget(self.table)
        self.table.setRowHeight(0, 200)  # 设置第一行的高度为50像素
        self.table.setColumnWidth(5, 100)  # 设置第一列宽度为100像素
        self.table.setRowCount(5)
        self.table.setColumnCount(3)
        for row in range(5):
            for col in range(3):
                item = QTableWidgetItem(f"Item {row},{col}")
                item.setTextAlignment(Qt.AlignCenter)  # 设置文本居中对齐
                self.table.setItem(row, col, item)

        # 隐藏行号和列号
        self.table.verticalHeader().setVisible(False)
        self.table.horizontalHeader().setVisible(True)

        # New button to process and display data
        self.processDataButton = QPushButton('Process Data')
        self.processDataButton.clicked.connect(self.processData)
        self.layout.addWidget(self.processDataButton)

    def loadDatabase(self):
        if not os.path.exists(self.db_path):
            QMessageBox.critical(self, 'Error', f'Database file not found: {self.db_path}')
            return

        try:
            self.connection = sqlite3.connect(self.db_path)
            self.cursor = self.connection.cursor()
            self.loadTable()
        except sqlite3.Error as e:
            QMessageBox.critical(self, 'Error', f'Failed to load database: {e}')

    def loadTable(self, query="SELECT * FROM parsed_sensor_info"):  # 确保这里的表名是正确的
        try:
            self.cursor.execute(query)
            data = self.cursor.fetchall()
            self.table.setRowCount(0)
            if data:
                self.table.setColumnCount(len(data[0]))
                self.table.setHorizontalHeaderLabels([description[0] for description in self.cursor.description])
                for row_number, row_data in enumerate(data):
                    self.table.insertRow(row_number)
                    for column_number, cell_data in enumerate(row_data):
                        self.table.setItem(row_number, column_number, QTableWidgetItem(str(cell_data)))
            else:
                self.table.setColumnCount(0)
        except sqlite3.Error as e:
            QMessageBox.critical(self, 'Error', f'Failed to execute query: {e}')

    def search(self):
        keyword = self.searchInput.text()
        query = f"SELECT * FROM parsed_sensor_info WHERE devaddr LIKE '%{keyword}%'"
        self.loadTable(query)

    def processData(self):
        # Call the processing code
        sensor_data = get_sensor_data(self.db_path)
        if sensor_data:
            timeout_counts, no_timeout_sensors = calculate_timeout(sensor_data)
            # Prepare output for display
            data_lines = [f"Sensor {addr}: {count} timeouts" for addr, count in timeout_counts.items()]
            data_lines.append(f"No timeout sensors: {no_timeout_sensors}")
            # Show in new window
            processing_window = ProcessingWindow(data_lines, self)
            processing_window.exec_()
        else:
            QMessageBox.information(self, 'Information', 'No data to process.')

    def showDetails(self, row, column):
        row_data = []
        for col in range(self.table.columnCount()):
            item = self.table.item(row, col)
            if item is not None:
                row_data.append(item.text())
            else:
                row_data.append('')

        headers = [self.table.horizontalHeaderItem(i).text() for i in range(self.table.columnCount())]
        details_window = DetailsWindow(row_data, headers, self)
        details_window.exec_()

    def get_sensor_data(db_path):
        conn = sqlite3.connect(db_path)
        c = conn.cursor()
        try:
            # 获取每个传感器的上传时间
            c.execute('SELECT sensor_address, upload_time FROM parsed_sensor_info ORDER BY id, upload_time')
            data = c.fetchall()
            return data
        except sqlite3.Error as e:
            print("数据库出现错误", e)
            return []
        finally:
            conn.close()

    def calculate_timeout(sensor_address, timeout_seconds=45):
        from collections import defaultdict

        # 使用字典存储每个传感器的上传时间
        sensor_times = defaultdict(list)
        for sensor_address, upload_time in sensor_address:
            sensor_times[sensor_address].append(upload_time)

        timeout_counts = defaultdict(int)
        no_timeout_sensors = 0

        # 计算超时次数
        for sensor_address, times in sensor_times.items():
            count = 0
            for i in range(len(times) - 1):
                datetime1 = datetime.strptime(times[i], '%Y-%m-%d %H:%M:%S')
                datetime2 = datetime.strptime(times[i + 1], '%Y-%m-%d %H:%M:%S')
                datetime1_str = datetime1.second
                datetime2_str = datetime2.second
                difference = datetime2_str - datetime1_str
                if difference > timeout_seconds:
                    count += 1

            if count > 0:
                timeout_counts[sensor_address] = count
            else:
                no_timeout_sensors += 1

        return timeout_counts, no_timeout_sensors

def create_new_table(db_path):
    download_file_from_server(hostname, port, username, password, remote_file_path, local_file_path)
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    try:
        # 创建新表
        c.execute('''
            CREATE TABLE parsed_sensor_info (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                devaddr INTEGER,
                dev_type TEXT,
                rssi TEXT,
                sensor_value TEXT,
                battery_percentage TEXT,
                sensor_light TEXT,
                firmware_version TEXT,
                hardware_version TEXT,
                number_of_abnormal TEXT,
                abnormals TEXT,
                date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        conn.commit()
    except sqlite3.Error as e:
        print(f"Error creating table: {e}")
    finally:
        conn.close()

#def migrate_data_and_drop_old_table(db_path, sensor_info, parsed_sensor_info):
  #  conn = sqlite3.connect(db_path)
 #   c = conn.cursor()
#
#    try:
#        # 迁移数据
#        c.execute(f'''
#            INSERT INTO parsed_sensor_info (upload_time)
#            SELECT upload_time
#            FROM sensor_info
#            ORDER BY upload_time
#        ''')
 #   except sqlite3.Error as e:
 #       print(f"SQL error: {e}")
 #   finally:
#        conn.close()

def insert_parsed_data(db_path, parsed_data_list):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    try:
        # 插入解析数据
        for parsed_data in parsed_data_list:
            c.execute('''
                INSERT INTO parsed_sensor_info (
                    devaddr, dev_type, 
                    rssi, sensor_value, battery_percentage, sensor_light, firmware_version, 
                    hardware_version, number_of_abnormal, abnormals, date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                parsed_data.get('devaddr', ''),
                parsed_data.get('dev_type', ''),
                parsed_data.get('rssi', ''),
                str(parsed_data.get('sensor_value', '')),
                parsed_data.get('battery_percentage', ''),
                parsed_data.get('sensor_light', ''),
                parsed_data.get('firmware_version', ''),
                parsed_data.get('hardware_version', ''),
                parsed_data.get('number_of_abnormal', ''),
                parsed_data.get('abnormals', ''),
                parsed_data.get('date')
            ))
        conn.commit()
    except sqlite3.Error as e:
        print(f"Error inserting data: {e}")
    finally:
        conn.close()


def process_data(db_path):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    try:
        # 读取 row_command 列数据
        c.execute('SELECT row_command FROM sensor_info')
        rows = c.fetchall()

        parsed_data_list = []
        for row in rows:
            command = row[0].replace(' ', '')
            parser = CommandParse(command)
            parsed_data = parser.parse_result
            parsed_data_list.append(parsed_data)

        # 创建新表
        create_new_table(db_path)

        # 插入解析数据到新表
        insert_parsed_data(db_path, parsed_data_list)

    except sqlite3.Error as e:
        print(f"Error processing data: {e}")
    finally:
        conn.close()

def get_sensor_data(db_path):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    try:
        # 获取每个传感器的上传时间
        c.execute('SELECT sensor_address, upload_time FROM sensor_info ORDER BY id, upload_time')
        data = c.fetchall()
        return data
    except sqlite3.Error as e:
        print("数据库出现错误", e)
        return []
    finally:
        conn.close()


def calculate_timeout(sensor_address, timeout_seconds=45):
    from collections import defaultdict

    # 使用字典存储每个传感器的上传时间
    sensor_times = defaultdict(list)
    for sensor_address, upload_time in sensor_address:
        sensor_times[sensor_address].append(upload_time)

    timeout_counts = defaultdict(int)
    no_timeout_sensors = 0

    # 计算超时次数
    for sensor_address, times in sensor_times.items():
        count = 0
        for i in range(len(times) - 1):
            datetime1 = datetime.strptime(times[i], '%Y-%m-%d %H:%M:%S')
            datetime2 = datetime.strptime(times[i+1], '%Y-%m-%d %H:%M:%S')
            datetime1_str = datetime1.second
            datetime2_str = datetime2.second
            difference = datetime2_str - datetime1_str
            if difference > timeout_seconds:
                count += 1

        if count > 0:
            timeout_counts[sensor_address] = count
        else:
            no_timeout_sensors += 1

    return timeout_counts, no_timeout_sensors


def main():
    db_path = '/Users/tancongyuan/Desktop/sensor_data.db'
    sensor_data = get_sensor_data(db_path)

    if sensor_data:
        timeout_counts, no_timeout_sensors = calculate_timeout(sensor_data)
        print(f"超时次数: {timeout_counts}")
        print(f"未超时传感器数量: {no_timeout_sensors}")
    else:
        print("未能从数据库中获取数据")

if __name__ == '__main__':
    hostname = '192.168.31.65'  # 远程服务器的 IP 地址或主机名
    port = 22  # SSH 默认端口是 22
    username = 'hdxt'  # 你的服务器用户名
    password = '111111'  # 你的服务器密码
    remote_file_path = '/usr/local/src/second-generation-underground/sensor_data.db'  # 服务器上的文件路径
    local_file_path = '/Users/tancongyuan/Desktop/sensor_data.db'  # 本地保存的文件路径
    db_path = '/Users/tancongyuan/Desktop/sensor_data.db'  # 数据库路径
    process_data(db_path)
    app = QApplication(sys.argv)
    viewer = DatabaseViewer(db_path)
    viewer.show()
    sys.exit(app.exec_())